ALTER TABLE Business 
ADD City VARCHAR(50) NOT NULL DEFAULT(''),
	StateCode VARCHAR(2) NOT NULL DEFAULT(''),
	ZipCode VARCHAR(5) NOT NULL DEFAULT(''),
	[Description] VARCHAR(MAX) NOT NULL DEFAULT('')
GO
CREATE TABLE [dbo].[BusinessHours](
	[BusinessId] [int] NOT NULL,
	[WeekDay] [char](3) NOT NULL,
	[FromTime] [datetime] NOT NULL,
	[ToTime] [datetime] NOT NULL,
	[Closed] [bit] NOT NULL,
 CONSTRAINT [PK_BusinessHours] PRIMARY KEY CLUSTERED 
(
	[BusinessId] ASC,
	[WeekDay] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Default [DF_BusinessHours_FlagClose]    Script Date: 12/28/2012 14:08:36 ******/
ALTER TABLE [dbo].[BusinessHours] ADD  CONSTRAINT [DF_BusinessHours_FlagClose]  DEFAULT ((0)) FOR [Closed]
GO
/****** Object:  ForeignKey [FK_BusinessHours_Business]    Script Date: 12/28/2012 14:08:36 ******/
ALTER TABLE [dbo].[BusinessHours]  WITH CHECK ADD  CONSTRAINT [FK_BusinessHours_Business] FOREIGN KEY([BusinessId])
REFERENCES [dbo].[Business] ([BusinessId])
GO
ALTER TABLE [dbo].[BusinessHours] CHECK CONSTRAINT [FK_BusinessHours_Business]
GO
CREATE TABLE [dbo].[BusinessCategory](
	[BusinessId] [int] NOT NULL,
	[CategoryId] [int] NOT NULL,
 CONSTRAINT [PK_BusinessCategory] PRIMARY KEY CLUSTERED 
(
	[BusinessId] ASC,
	[CategoryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  ForeignKey [FK_BusinessCategory_Category]    Script Date: 12/28/2012 14:33:34 ******/
ALTER TABLE [dbo].[BusinessCategory]  WITH CHECK ADD  CONSTRAINT [FK_BusinessCategory_Category] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Category] ([CategoryId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[BusinessCategory] CHECK CONSTRAINT [FK_BusinessCategory_Category]
GO
/****** Object:  ForeignKey [FK_BusinessCategory_Business]    Script Date: 12/28/2012 14:33:34 ******/
ALTER TABLE [dbo].[BusinessCategory]  WITH CHECK ADD  CONSTRAINT [FK_BusinessCategory_Business] FOREIGN KEY([BusinessId])
REFERENCES [dbo].[Business] ([BusinessId])
GO
ALTER TABLE [dbo].[BusinessCategory] CHECK CONSTRAINT [FK_BusinessCategory_Business]
GO
ALTER TABLE Business 
ADD CreditCardNo VARCHAR(20) NOT NULL DEFAULT(''),
	CreditCardExpDate DATETIME NOT NULL DEFAULT(''),
	CreditCardName VARCHAR(50) NOT NULL DEFAULT(''),
	CreditCardPin VARCHAR(10) NOT NULL DEFAULT('')
GO
ALTER TABLE Business 
ADD BillingAddress1 VARCHAR(100) NOT NULL DEFAULT(''),
	BillingAddress2 VARCHAR(100) NOT NULL DEFAULT(''),
	BillingCity VARCHAR(50) NOT NULL DEFAULT(''),
	BillingStateCode VARCHAR(2) NOT NULL DEFAULT(''),
	BillingZipCode VARCHAR(5) NOT NULL DEFAULT(''),
	BillingUse BIT NOT NULL DEFAULT(0)
GO
CREATE PROCEDURE spBusiness_Update
(
	@BusinessId INT = NULL,
	
	@Login VARCHAR(50),
	@Email VARCHAR(50),
	@Password VARCHAR(50),
	@BusinessName VARCHAR(100),
	@Web VARCHAR(100),
	@BusinessPhone VARCHAR(50),
	@CellPhone VARCHAR(50),
	@TollFreePhone VARCHAR(50),
	@Fax VARCHAR(50),
	@StreetAddress1 VARCHAR(100),
	@StreetAddress2 VARCHAR(100),
	@City VARCHAR(50),
	@StateCode VARCHAR(2),
	@ZipCode VARCHAR(5),
	@Description VARCHAR(MAX),
	
	@CreditCardNo VARCHAR(20),
	@CreditCardExpDate DATETIME,
	@CreditCardName VARCHAR(50),
	@CreditCardPin VARCHAR(10),
	
	@BillingAddress1 VARCHAR(100),
	@BillingAddress2 VARCHAR(100),
	@BillingCity VARCHAR(50),
	@BillingStateCode VARCHAR(2),
	@BillingZipCode VARCHAR(5),
	@BillingUse BIT,
	
	@SunFrom DATETIME,
	@SunTo DATETIME,
	@SunClosed BIT,
	@MonFrom DATETIME,
	@MonTo DATETIME,
	@MonClosed BIT,
	@TueFrom DATETIME,
	@TueTo DATETIME,
	@TueClosed BIT,
	@WedFrom DATETIME,
	@WedTo DATETIME,
	@WedClosed BIT,
	@ThuFrom DATETIME,
	@ThuTo DATETIME,
	@ThuClosed BIT,
	@FriFrom DATETIME,
	@FriTo DATETIME,
	@FriClosed BIT,
	@SatFrom DATETIME,
	@SatTo DATETIME,
	@SatClosed BIT,
	
	@Categories VARCHAR(MAX)
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	BEGIN TRANSACTION
		IF (@BusinessId IS NULL)
		BEGIN
			INSERT INTO UserBusinessLogins
			(LoginName, LoginType)
			VALUES(@Login, 'B')
			
			INSERT INTO Business
			([Login], Email, [Password], BusinessName, Web, BusinessPhone, CellPhone, TollFreePhone, Fax, StreetAddress1, StreetAddress2, 
			IsActive, Deleted, City, StateCode, ZipCode, [Description], 
			CreditCardNo, CreditCardExpDate, CreditCardName, CreditCardPin, 
			BillingAddress1, BillingAddress2, BillingCity, BillingStateCode, BillingZipCode, BillingUse)
			VALUES (@Login, @Email, @Password, @BusinessName, @Web, @BusinessPhone, @CellPhone, @TollFreePhone, @Fax, @StreetAddress1, @StreetAddress2, 
				1, 0, @City, @StateCode, @ZipCode, @Description, 
				@CreditCardNo, @CreditCardExpDate, @CreditCardName, @CreditCardPin, 
				@BillingAddress1, @BillingAddress2, @BillingCity, @BillingStateCode, @BillingZipCode, @BillingUse)
				
			SET @BusinessId = SCOPE_IDENTITY()
			
			INSERT INTO BusinessHours
			(BusinessId, [WeekDay], FromTime, ToTime, Closed)
			SELECT @BusinessId, 'Sun', @SunFrom, @SunTo, @SunClosed
			UNION
			SELECT @BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed
			UNION
			SELECT @BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed
			UNION
			SELECT @BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed
			UNION
			SELECT @BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed
			UNION
			SELECT @BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed
			UNION
			SELECT @BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed
		END
		ELSE BEGIN
			UPDATE Business
			SET [Login] = @Login, Email = @Email, [Password] = @Password, BusinessName = @BusinessName, Web = @Web, 
				BusinessPhone = @BusinessPhone, CellPhone = @CellPhone, TollFreePhone = @TollFreePhone, Fax = @Fax, 
				StreetAddress1 = @StreetAddress1, StreetAddress2 = @StreetAddress2, 
				City = @City, StateCode = @StateCode, ZipCode = @ZipCode, 
				[Description] = @Description, 
				CreditCardNo = @CreditCardNo, CreditCardExpDate = @CreditCardExpDate, CreditCardName = @CreditCardName, CreditCardPin = @CreditCardPin, 
				BillingAddress1 = @BillingAddress1, BillingAddress2 = @BillingAddress2, BillingCity = @BillingCity, BillingStateCode = @BillingStateCode, BillingZipCode = @BillingZipCode, BillingUse = @BillingUse
			WHERE (BusinessId = @BusinessId)
			
			UPDATE BusinessHours
			SET FromTime = vw.FromTime, ToTime = vw.ToTime, Closed = vw.Closed
			FROM (SELECT @BusinessId AS BusinessId, 'Sun' AS WD, @SunFrom AS FromTime, @SunTo AS ToTime, @SunClosed AS Closed
				UNION
				SELECT @BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed
				UNION
				SELECT @BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed
				UNION
				SELECT @BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed
				UNION
				SELECT @BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed
				UNION
				SELECT @BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed
				UNION
				SELECT @BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed) AS vw
			WHERE (BusinessHours.BusinessId = vw.BusinessId)
				AND (BusinessHours.[WeekDay] = vw.WD)
		END	
			
		
		DELETE 
		FROM BusinessCategory
		WHERE (BusinessId = @BusinessId)
			
		INSERT INTO BusinessCategory
		(BusinessId, CategoryId)
		SELECT @BusinessId, CONVERT(INT, Items.Item)
		FROM dbo.fnAPI_String_Split(@Categories, ',') AS Items
	
	COMMIT TRANSACTION
END
